package com.itheima.tliasproject.mapper;

import com.itheima.tliasproject.pojo.*;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface EmpMapper {
    //查询全部员工数据
    @Select("select e.*,d.name from emp e left join dept d on e.dept_id = d.id")
    List<Emp> FindAll();

    //获取总数
    @Select("select count(*) from emp")
    Long count();






//
//    //获取分页数据
//    @Select("select e.*,d.name deptName from emp e left join dept d on e.dept_id = d.id limit #{start},#{pageSize}")
//    List<Emp> list(Integer start, Integer pageSize);

    //使用插件来获取分页与数据条件查询在这里我们使用动态sql
    List<Emp> list(EmpQueryParam param);


    //新增员工
    @Options(useGeneratedKeys = true, keyProperty = "id")
    //获取到ID由于稍后，我们在保存工作经历信息的时候，需要记录是哪位员工的工作经历。
    // 所以，保存完员工信息之后，是需要获取到员工的ID的，那这里就需要通过Mybatis中提供的主键返回功能来获取。
    @Insert("insert into emp(username, name, gender, phone, job, salary, image, entry_date, dept_id, create_time, update_time) " +
            "values (#{username},#{name},#{gender},#{phone},#{job},#{salary},#{image},#{entryDate},#{deptId},#{createTime},#{updateTime})")
    void insert(Emp emp);


    void deleteBatch(List<Integer> ids);



    //回显方式一
//    @Select("select e.*, em.* from emp e left join emp_expr em on e.id = em.emp_id where e.id = #{id}")
//    Emp SelectById(Integer id);
//回显方式二
    @Select("select * from emp where id = #{id}")
    Emp SelectById2(Integer id);

    void update(Emp emp);

//查询全部员工信息
    @Select("select * from emp;")
    List<Emp> findall();

    //统计员工性别
//    @Select("SELECT gender name, COUNT(*) value FROM emp GROUP BY gender ")
    @Select("SELECT CASE gender WHEN 1 THEN ' 男性员工 ' WHEN 2 THEN ' 女性员工 ' ELSE ' 未知 ' END AS name, count(*) AS value FROM emp GROUP BY gender")
    List<InformationCount> selectgender();
//统计学生学历
    @Select("select case s.degree when 1 then '初中' when 2 then '高中' when 3 then ' 大专' when  4 then '本科' when 5 then '硕士' when 6 then '博士' else '未知' end name ,count(*) value from student s group by degree")
    List<InformationCount> stuinfo();
//员工职位信息统计


//    @Select("select case job when 1 then '班主任'   when 2 then '讲师'   when 3 then '学工主管' when 4 then '教研主管' when 5 then '咨询师' else '其他' end jobList,count(*) dataList from emp group by job order by dataList")
@Select("select " +
        "case job " +
        "when 1 then '班主任' " +
        "when 2 then '讲师' " +
        "when 3 then '学工主管' " +
        "when 4 then '教研主管' " +
        "when 5 then '咨询师' " +
        "else '其他' end as jobName, " +
        "count(*) as count " +
        "from emp " +
        "group by job " +
        "order by count")
List<Map<String, Object>> getJobInfoList();

//登录校验
    @Select("select * from emp where username = #{username} and password = #{password}")

    Emp login1(Emp emp);
}
